package db.jdbc;

import db.ActivityRepository;
import db.ClubRepository;
import domain.Activity;
import domain.Club;
import domain.ClubAdministrator;
import domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import web.PaginationSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

/**
 * 活动资源库接口的jdbc实现类
 *
 * @author liujiale
 */
@Repository
public class JdbcActivityRepository implements ActivityRepository {

    private static final String INSERT_ACTIVITY = "insert into activity(activityName,description,startTime,endTime,place) values(?,?,?,?,?)";
    private static final String INSERT_ACTIVITY_ORGANIZERS = "insert into activity_organizers(activityID,clubAdminID) values(?,?)";

    private static final String UPDATE_ACTIVITY = "update activity set description=?,startTime=?,endTime=?,place=? where activityID=?";
    private static final String DELETE_ACTIVITY = "delete from activity where activityID=?";
    private static final String DELETE_ACTIVITY_ORGANIZERS = "delete from activity_organizers where activityID=?";
    private static final String DELETE_CLUB_ACTIVITY = "delete from club_activities where activityID=?";
    private static final String DELETE_ACTIVITY_PARTICIPANTS = "delete from activity_participants where activityID=?";


    private static final String SELECT_ACTIVITY = "select * from activity";
    private static final String SELECT_ACTIVITY_BY_PAGINATION = SELECT_ACTIVITY + " order by activityID  limit ? offset  ?";
    private JdbcTemplate jdbc;
    @Autowired
    private ClubRepository clubRepository;

    @Autowired
    public JdbcActivityRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    /**
     * 获取活动的总数
     *
     * @return 活动的数量
     */
    @Override
    public long count() {
        return jdbc.queryForInt("select count(activityID) from activity");
    }

    /**
     * 获取特定社团中活动的总数
     *
     * @param clubID 社团ID
     * @return 社团中活动的数量
     */
    @Override
    public long countByClubID(int clubID) {
        return jdbc.queryForInt("select count(a.activityID) from activity as a,club_activities as ca where ca.clubID =?  and ca.activityID= a.activityID",clubID);
    }

    /**
     * 创建一个新的活动
     *
     * @param activity 新活动的信息
     * @return 新创建的活动
     */
    @Override
    public Activity save(Activity activity) {
        Vector<ClubAdministrator> organizerList = activity.getOrganizerList();
        int activityID = activity.getActivityID();
        for (ClubAdministrator organizer : organizerList) {
            int clubAdminID = organizer.getClubAdminID();
            jdbc.update(INSERT_ACTIVITY_ORGANIZERS, activityID, clubAdminID);
        }
        int clubID = clubRepository.viewClubInfo(organizerList.get(0).getClubAdminID()).getClubID();
        jdbc.update("insert into club_activities values (?,?) " ,clubID, activityID);
//        jdbc.update(INSERT_ACTIVITY, activity.getActivityName(),activity.getDescription(),
//                activity.getStartTime(), activity.getEndTime(), activity.getPlace());
        return activity;
    }

    /**
     * 根据ID查找活动
     *
     * @param activityID@return 对应ID的活动
     */
    @Override
    public Activity findOne(int activityID) {
        Activity activity = null;
        List<User> participants=null;
        List<ClubAdministrator> organizers=null;
        try {
            activity = jdbc.queryForObject(SELECT_ACTIVITY + " where activityID=?", new ActivityRowMapper(), activityID);
            participants=jdbc.query("select u.* from _user as u,activity_participants as ap where ap.activityID=? and ap.userID=u.userID", new JdbcUserRepository.UserRowMapper(),activityID);
            organizers=jdbc.query("select ca.* from clubAdministrator as ca,activity_organizers as ao where ao.activityID=? and ao.clubAdminID=ca.clubAdminID", new JdbcClubAdministratorRepository.ClubAdministratorRowMapper(),activityID);
            Vector<User> participantList=new Vector<>(participants);
            Vector<ClubAdministrator> organizerList=new Vector<>(organizers);
            activity.setParticipantList(participantList);
            activity.setOrganizerList(organizerList);
        } catch (DataAccessException e) {
        }
        return activity;
    }

    /**
     * 根据活动名查找活动
     *
     * @param activityName 活动名
     * @return 对应活动名的活动
     */
    @Override
    public Activity findByActivityName(String activityName) {
        Activity activity = null;
        try {
            activity = jdbc.queryForObject(SELECT_ACTIVITY + " where activityName=?", new ActivityRowMapper(), activityName);
        } catch (DataAccessException e) {
        }
        return activity;
    }

    /**
     * 分页查找活动
     *
     * @param pageNo   页码
     * @param pageSize 每页的数量
     * @return 分页的活动列表
     */
    @Override
    public PaginationSupport<Activity> findPage(int pageNo, int pageSize) {
        int totalCount = (int) count();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1) {
            return new PaginationSupport<Activity>(new ArrayList<Activity>(0), 0);
        }

        List<Activity> items = jdbc.query(SELECT_ACTIVITY_BY_PAGINATION, new ActivityRowMapper(), pageSize, startIndex);
        PaginationSupport<Activity> ps = new PaginationSupport<Activity>(items, totalCount, pageSize, startIndex);
        return ps;
    }

    /**
     * 更新活动
     *
     * @param activity 更新后的活动信息
     * @return 是否更新成功
     */
    @Override
    public boolean updateActivity(Activity activity) {
        return jdbc.update(UPDATE_ACTIVITY, activity.getDescription(),
                activity.getStartTime(), activity.getEndTime(),
                activity.getPlace(), activity.getActivityID()) != 0;
    }

    /**
     * 删除活动
     *
     * @param activityID
     * @return 是否删除成功
     */
    @Override
    public boolean deleteActivity(int activityID) {
        jdbc.update(DELETE_CLUB_ACTIVITY, activityID);
        jdbc.update(DELETE_ACTIVITY_PARTICIPANTS, activityID);
        return jdbc.update(DELETE_ACTIVITY_ORGANIZERS, activityID) != 0 ;
    }

    /**
     * 根据用户查找用户所以活动
     *
     * @param userId 用户ID
     * @return 用户的活动列表
     */
    @Override
    public List<Activity> findActivityByUserId(int userId) {
        return jdbc.query("select a.* from activity as a,activity_participants as ap where ap.userID= ? and ap.activityID=a.activityID", new ActivityRowMapper(), userId);
    }

    /**
     * 获取所有活动
     *
     * @return 所有活动的列表
     */
    @Override
    public List<Activity> findAll() {
        return jdbc.query(SELECT_ACTIVITY + " order by activityID", new ActivityRowMapper());
    }

    /**
     * 获取社团中所有活动
     *
     * @param club 社团
     * @return 社团中所有活动
     */
    @Override
    public List<Activity> findActivitiesByClub(Club club) {
        return jdbc.query("select a.* from activity as a,club_activities as ca where ca.clubID= ? and ca.activityID=a.activityID", new ActivityRowMapper(), club.getClubID());
    }

    static class ActivityRowMapper implements RowMapper<Activity> {
        /**
         * 将数据库查询结果映射到Activity对象
         *
         * @param rs 查询结果集
         * @param rowNum 行号
         * @return Activity对象
         * @throws SQLException SQL异常
         */
        public Activity mapRow(ResultSet rs, int rowNum) throws SQLException {
            // 从结果集获取activity字段，并构造Activity对象
            return new Activity(rs.getInt("activityID"),
                    rs.getString("activityName"),
                    rs.getString("description"),
                    rs.getTimestamp("startTime"),
                    rs.getTimestamp("endTime"),
                    rs.getString("place"));
        }
    }
}
